package net.zjitc.dao.impl;


import net.zjitc.dao.AdminProductDao;
import net.zjitc.pojo.CategorySecond;
import net.zjitc.pojo.Evaluate;
import net.zjitc.pojo.Product;
import net.zjitc.pojo.User;
import net.zjitc.utils.PageBean;
import net.zjitc.utils.db.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/11/22.
 */
public class AdminProductDaoImpl implements AdminProductDao{
    public PageBean<Product> findPage(int page, int pageSize) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);
        String sql="select count(*) from product";
        String listsql="select * from product order by pid limit ?,?";
        try {
            Number num= (Number) runner.query(sql,new ScalarHandler());
            int count=num.intValue();
            pageBean.setCount(count);

            List<Product> list= (List<Product>) runner.query(listsql,new BeanListHandler(Product.class),(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int addProduct(Product product) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="insert into product(pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) values(?,?,?,?,?,?,?,?)";
        try {
            int count=runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public Product findByPid(String pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product where pid=?";
        try {
            Map<String,Object> map=runner.query(sql,new MapHandler(),pid);
            Product product=new Product();
            CategorySecond categorySecond=new CategorySecond();
            BeanUtils.populate(product,map);
            BeanUtils.populate(categorySecond,map);
            product.setCategorySecond(categorySecond);
            return product;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public int editProduct(Product product) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="update product set pname=?,market_price=?,shop_price=?,image=?,pdesc=?,is_hot=?,pdate=?,csid=? where pid=?";
        try {
            int count=runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid(),product.getPid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteProduct(String pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="delete from product where pid=?";
        try {
            int count=runner.update(sql,pid);
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public PageBean<Product> findPageBeanByCid(int page, int pageSize, int cid) {
        PageBean<Product> bean = new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sqlCount = "SELECT COUNT(*) FROM product p JOIN categorysecond cs,category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND c.cid =?";
        String sql = "SELECT p.*FROM product p JOIN categorysecond cs,category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND c.cid = ? LIMIT ?,? ";
        //查询有多少条数据
        try{
            Number num = (Number) runner.query(sqlCount,new ScalarHandler(),cid);
            bean.setCount(num.intValue());

            List<Product> list = runner.query(sql,new BeanListHandler<Product>(Product.class),cid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PageBean<Product> findPageBeanByCsid(int page, int pageSize, int csid) {
        PageBean<Product> bean = new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sqlCount = "SELECT COUNT(*) FROM product p JOIN categorysecond cs,category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND cs.csid =?";
        String sql = "SELECT p.*FROM product p JOIN categorysecond cs,category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND cs.csid = ? LIMIT ?,? ";
        //查询有多少条数据
        try{
            Number num = (Number) runner.query(sqlCount,new ScalarHandler(),csid);
            bean.setCount(num.intValue());

            List<Product> list = runner.query(sql,new BeanListHandler<Product>(Product.class),csid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public Product findBYPid(int pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where pid = ?";
        try {
            Product product = runner.query(sql,new BeanHandler<Product>(Product.class),pid);
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Product> findHot() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where is_hot = 1 limit 10";
        try {
            return runner.query(sql, new BeanListHandler<Product>(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Product> findNew() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product order by pdate asc limit 10";
        try {
            return runner.query(sql, new BeanListHandler<Product>(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Evaluate> findEvaluateByPid(int pid){
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT * FROM evaluate e JOIN product p,USER u WHERE e.pid = p.pid AND e.uid = u.uid AND p.pid = ?";
        List<Evaluate> evaluateList = new ArrayList<Evaluate>();
        List<Map<String,Object>> list = new ArrayList<Map<String, Object>>();
        try{
            list = runner.query(sql,new MapListHandler(),pid);
            for (Map<String,Object> m : list){
                User user = new User();
                Product product = new Product();
                Evaluate evaluate = new Evaluate();
                BeanUtils.populate(user,m);
                BeanUtils.populate(product,m);
                BeanUtils.populate(evaluate,m);
                evaluate.setUser(user);
                evaluate.setProduct(product);
                evaluateList.add(evaluate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  evaluateList;
    }

   /* public static void main(String[] args) {
        AdminProductDaoImpl dao = new AdminProductDaoImpl();
        List<Evaluate> list = dao.findEvaluateByPid(1);
        System.out.println(list);
    }*/
}
